﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using CardLogistics.Model.PutInCard;

namespace CardLogistics.DAL.PutInCard
{
    /// <summary>
    /// 半成品/成品卡入库发卡文件_数据访问类
    /// </summary>
    public class PutInCardData
    {
        #region 查询
        /// <summary>
        /// 根据条件查询相应的表的字段
        /// </summary>
        /// <param name="StrWhere">查询条件</param>
        /// <param name="StrFields">要查询的字段</param>
        /// <returns>填充完数据的DataTable</returns>
        public DataTable GetList(string StrWhere, string StrFields)
        {
            return CommonData.GetListOrderBy(StrWhere, "v_PutInCard", StrFields, "ID", 0);
        }

        /// <summary>
        /// 根据编号查询基本信息
        /// </summary>
        /// <param name="id">编号</param>
        /// <returns>填充完数据的DataTable</returns>
        public DataTable GetInfoById(string id)
        {
            return GetList(" ID = '" + id + "' ", "*");
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public CardLogistics.Model.PutInCard.PutInCard GetModel(string rId)
        {
            CardLogistics.Model.PutInCard.PutInCard model = new CardLogistics.Model.PutInCard.PutInCard();
            DataRow row = GetInfoById(rId).Rows[0];
            if (row != null)
            {
                if (row["ID"] != null)
                {
                    model.ID = row["ID"].ToString();
                }
                if (row["VERSION"] != null)
                {
                    model.VERSION = row["VERSION"].ToString();
                }
                if (row["CARDKIND"] != null && row["CARDKIND"].ToString() != "")
                {
                    model.CARDKIND = row["CARDKIND"].ToString();
                }
                if (row["BATCHID"] != null)
                {
                    model.BATCHID = row["BATCHID"].ToString();
                }
                if (row["CARDBEGINSSN"] != null)
                {
                    model.CARDBEGINSSN = row["CARDBEGINSSN"].ToString();
                }
                if (row["CARDENDSSN"] != null)
                {
                    model.CARDENDSSN = row["CARDENDSSN"].ToString();
                }
                if (row["RECNUM"] != null && row["RECNUM"].ToString() != "")
                {
                    model.RECNUM = row["RECNUM"].ToString();
                }
                if (row["PUTINNO"] != null)
                {
                    model.PUTINNO = row["PUTINNO"].ToString();
                }
                if (row["PUTINDATE"] != null && row["PUTINDATE"].ToString() != "")
                {
                    model.PUTINDATE = DateTime.Parse(row["PUTINDATE"].ToString());
                }
                if (row["CREATEUSER"] != null)
                {
                    model.CREATEUSER = row["CREATEUSER"].ToString();
                }
                if (row["CREATEDATE"] != null && row["CREATEDATE"].ToString() != "")
                {
                    model.CREATEDATE = DateTime.Parse(row["CREATEDATE"].ToString());
                }
                if (row["REMARK"] != null)
                {
                    model.REMARK = row["REMARK"].ToString();
                }
                if (row["IsSubmit"] != null)
                {
                    model.IsSubmit = int.Parse(row["IsSubmit"].ToString());
                }
                if (row["OUTSTORAGEID"] != null)
                {
                    model.OUTSTORAGEID = row["OUTSTORAGEID"].ToString();
                }
                if (row["OUTSTORAGENO"] != null)
                {
                    model.OUTSTORAGENO = row["OUTSTORAGENO"].ToString();
                }
            }
            return model;
        }
        #endregion

        #region 重载新增发卡操作
        /// <summary>
        /// 重载新增发卡操作
        /// </summary>
        /// <param name="model">发卡文件实体</param>	
        /// <param name="dt">存储子表信息</param>
        public string Add(CardLogistics.Model.PutInCard.PutInCard model, DataTable dt)
        {
            string StrOutPar = string.Empty;//返回值
            int val = 0;
            string sqlConn = DbHelperSQL.ConnectionString;
            using (OracleConnection conn = new OracleConnection(sqlConn))
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                OracleTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    string gid = System.Guid.NewGuid().ToString();//主键Id
                    StringBuilder strSql = new StringBuilder(" begin ");
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        DataRow dr = dt.Rows[i];

                        #region 半成品/成品入库子表
                        OracleParameter[] parameter = {
                        new OracleParameter("varOut", OracleType.VarChar,50),
                        new OracleParameter("vDevId", OracleType.VarChar,50),
                        new OracleParameter("vOprId", OracleType.VarChar,50),
                        new OracleParameter("vIssDate", OracleType.VarChar,20),
                        new OracleParameter("vExpDate", OracleType.VarChar,20),
                        new OracleParameter("vCardId", OracleType.VarChar,50),
                        new OracleParameter("vCardSSN", OracleType.VarChar,50),
                        new OracleParameter("vCardCSN", OracleType.VarChar,50),
                        new OracleParameter("vCardType", OracleType.VarChar,50),
                        new OracleParameter("vChildCardType", OracleType.VarChar,50),
                        new OracleParameter("vCardMac", OracleType.VarChar,50),
                        new OracleParameter("vFgvAmt", OracleType.VarChar,50),
                        new OracleParameter("vInitAmt", OracleType.VarChar,50),
                        new OracleParameter("vIniFlag",OracleType.VarChar,50),
                        new OracleParameter("vReserved",OracleType.VarChar,50),
                        new OracleParameter("vPutInId",OracleType.VarChar,50)};
                        parameter[0].Direction = ParameterDirection.Output;
                        parameter[1].Value = dr["DevId"];
                        parameter[2].Value = dr["OprId"];
                        parameter[3].Value = dr["IssDate"];
                        parameter[4].Value = dr["ExpDate"];
                        parameter[5].Value = dr["CardId"];
                        parameter[6].Value = dr["CardSSN"];
                        parameter[7].Value = dr["CardCSN"];
                        parameter[8].Value = dr["CardType"];
                        parameter[9].Value = dr["ChildCardType"];
                        parameter[10].Value = dr["CardMac"];
                        parameter[11].Value = dr["FgvAmt"];
                        parameter[12].Value = dr["InitAmt"];
                        parameter[13].Value = dr["IniFlag"];
                        parameter[14].Value = dr["Reserved"];
                        parameter[15].Value = gid;//外键Id
                        DbHelperSQL.RunProcedure("P_PutInCard_Detail_Add", parameter, parameter[0]).ToString();
                        #endregion
                    }
                    strSql.Append(" end;");

                    #region 分解插入卡明细
                    
                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        StringBuilder sbSql = new StringBuilder(" begin ");
                        sbSql.Append(" update CARDRECORD set BCPRUKUSJ=to_date('" + model.PUTINDATE.ToString() + "','yyyy-MM-dd HH24:mi:ss'),BCPRUKUID='" + gid + "' where CARDNO=LPAD(" + dt.Rows[j]["CardId"].ToString() + ",8,'0') and ISEC=(select max(ISEC) from CARDRECORD where CARDNO=LPAD(" + dt.Rows[j]["CardId"].ToString() + ",8,'0'));");
                        sbSql.Append(" end;");
                        if (!String.IsNullOrEmpty(sbSql.ToString()))
                        {
                            cmd.CommandText = sbSql.ToString();
                            val += cmd.ExecuteNonQuery();
                        }
                    }
                    #endregion

                    OracleParameter[] parameters = {
                    new OracleParameter("varOut", OracleType.VarChar,50),
					new OracleParameter("vVERSION", OracleType.VarChar,50),
					new OracleParameter("vCARDKIND", OracleType.VarChar,10),
					new OracleParameter("vBATCHID", OracleType.VarChar,20),
					new OracleParameter("vCARDBEGINSSN", OracleType.VarChar,20),
					new OracleParameter("vCARDENDSSN", OracleType.VarChar,20),
					new OracleParameter("vRECNUM", OracleType.VarChar,20),
					new OracleParameter("vPUTINNO", OracleType.NVarChar),
					new OracleParameter("vPUTINDATE", OracleType.DateTime),
					new OracleParameter("vCREATEUSER", OracleType.VarChar,20),
					new OracleParameter("vCREATEDATE", OracleType.DateTime),
					new OracleParameter("vREMARK", OracleType.NVarChar),
                    new OracleParameter("vIssubmit", OracleType.Number),
                    new OracleParameter("vOUTSTORAGEID",OracleType.VarChar),
                    new OracleParameter("vID",OracleType.VarChar)};
                    parameters[0].Direction = ParameterDirection.Output;
                    parameters[1].Value = model.VERSION;
                    parameters[2].Value = model.CARDKIND;
                    parameters[3].Value = model.BATCHID;
                    parameters[4].Value = model.CARDBEGINSSN;
                    parameters[5].Value = model.CARDENDSSN;
                    parameters[6].Value = model.RECNUM;
                    parameters[7].Value = model.PUTINNO;
                    parameters[8].Value = model.PUTINDATE;
                    parameters[9].Value = model.CREATEUSER;
                    parameters[10].Value = model.CREATEDATE;
                    parameters[11].Value = model.REMARK;
                    parameters[12].Value = model.IsSubmit;
                    parameters[13].Value = model.OUTSTORAGEID;
                    parameters[14].Value = gid;

                    StrOutPar = DbHelperSQL.RunProcedure("P_PutInCard_Add", parameters, parameters[0]).ToString();
                    if (StrOutPar.Split(',')[0] == "ok")
                    {
                        tx.Commit();

                        StringBuilder sbHK = new StringBuilder();//将制卡坏卡插入缺失卡流水表中
                        sbHK.Append(" insert into CARDRECORD_LACK(LCARDNO,ZKID) select t.cardno,'" + gid + "' HKID from cardrecord t   ");
                        sbHK.Append(" where t.bkchukusj is not null and t.bcprukusj is null and ");
                        sbHK.Append("BKCHUKUID=(select OUTSTORAGEID from PUTINCARD where ID='" + gid + "')");
                        cmd.CommandText = sbHK.ToString();
                        cmd.ExecuteNonQuery();
                    }
                    else
                        tx.Rollback();
                }
                catch (System.Data.OracleClient.OracleException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
            }
            return StrOutPar;
        }
        #endregion

        #region 重载修改发卡操作
        /// <summary>
        /// 重载修改发卡操作
        /// </summary>
        /// <param name="model">发卡文件实体</param>	
        /// <param name="dt">存储子表信息</param>
        public string Update(CardLogistics.Model.PutInCard.PutInCard model, DataTable dt)
        {
            string StrOutPar = string.Empty;//返回值

            int val = 0;
            string sqlConn = DbHelperSQL.ConnectionString;
            using (OracleConnection conn = new OracleConnection(sqlConn))
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                OracleTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    //循环插入子表信息
                    StringBuilder strSql = new StringBuilder(" begin ");//初始化
                    strSql.Append(" delete from PutInCard_Detail where PutInId='" + model.ID + "';");//修改之前删除子表信息
                    strSql.Append(" update CARDRECORD set BCPRUKUSJ=null,BCPRUKUID=null where BCPRUKUID='" + model.ID + "'; ");//还原卡流水
                    strSql.Append(" delete from cardrecord_lack where ZKID='" + model.ID + "';");//修改之前删除卡流水-缺卡卡号
                    strSql.Append(" end;");
                    if (!String.IsNullOrEmpty(strSql.ToString()))
                    {
                        cmd.CommandText = strSql.ToString();
                        val += cmd.ExecuteNonQuery();
                    }
                    strSql = new StringBuilder(" begin ");//初始化
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        DataRow dr = dt.Rows[i];

                        #region 半成品/成品入库子表(修改=删除操作)
                        OracleParameter[] parameter = {
                    new OracleParameter("varOut", OracleType.VarChar,50),
                    new OracleParameter("vDevId", OracleType.VarChar,50),
                    new OracleParameter("vOprId", OracleType.VarChar,50),
                    new OracleParameter("vIssDate", OracleType.VarChar,20),
                    new OracleParameter("vExpDate", OracleType.VarChar,20),
                    new OracleParameter("vCardId", OracleType.VarChar,50),
                    new OracleParameter("vCardSSN", OracleType.VarChar,50),
                    new OracleParameter("vCardCSN", OracleType.VarChar,50),
                    new OracleParameter("vCardType", OracleType.VarChar,50),
                    new OracleParameter("vChildCardType", OracleType.VarChar,50),
                    new OracleParameter("vCardMac", OracleType.VarChar,50),
                    new OracleParameter("vFgvAmt", OracleType.VarChar,50),
                    new OracleParameter("vInitAmt", OracleType.VarChar,50),
                    new OracleParameter("vIniFlag",OracleType.VarChar,50),
                    new OracleParameter("vReserved",OracleType.VarChar,50),
                    new OracleParameter("vPutInId",OracleType.VarChar,50)};
                        parameter[0].Direction = ParameterDirection.Output;
                        parameter[1].Value = dr["DevId"];
                        parameter[2].Value = dr["OprId"];
                        parameter[3].Value = dr["IssDate"];
                        parameter[4].Value = dr["ExpDate"];
                        parameter[5].Value = dr["CardId"];
                        parameter[6].Value = dr["CardSSN"];
                        parameter[7].Value = dr["CardCSN"];
                        parameter[8].Value = dr["CardType"];
                        parameter[9].Value = dr["ChildCardType"];
                        parameter[10].Value = dr["CardMac"];
                        parameter[11].Value = dr["FgvAmt"];
                        parameter[12].Value = dr["InitAmt"];
                        parameter[13].Value = dr["IniFlag"];
                        parameter[14].Value = dr["Reserved"];
                        parameter[15].Value = model.ID;//外键Id
                        DbHelperSQL.RunProcedure("P_PutInCard_Detail_Add", parameter, parameter[0]).ToString();
                        #endregion
                    }
                    strSql.Append(" end; ");

                    #region 分解插入卡明细
                    StringBuilder sbSql = new StringBuilder(" begin ");
                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        sbSql.Append(" update CARDRECORD set BCPRUKUSJ=to_date('" + model.PUTINDATE.ToString() + "','yyyy-MM-dd HH24:mi:ss'),BCPRUKUID='" + model.ID + "' where CARDNO=LPAD(" + dt.Rows[j]["CardId"].ToString() + ",8,'0') and ISEC=(select max(ISEC) from CARDRECORD where CARDNO=LPAD(" + dt.Rows[j]["CardId"].ToString() + ",8,'0'));");
                    }
                    sbSql.Append(" end;");
                    if (!String.IsNullOrEmpty(sbSql.ToString()))
                    {
                        cmd.CommandText = sbSql.ToString();
                        val += cmd.ExecuteNonQuery();
                    }
                    #endregion

                    OracleParameter[] parameters = {
				    new OracleParameter("vID", OracleType.VarChar,50),
					new OracleParameter("vVERSION", OracleType.VarChar,50),
					new OracleParameter("vCARDKIND", OracleType.VarChar,10),
					new OracleParameter("vBATCHID", OracleType.VarChar,20),
					new OracleParameter("vCARDBEGINSSN", OracleType.VarChar,20),
					new OracleParameter("vCARDENDSSN", OracleType.VarChar,20),
					new OracleParameter("vRECNUM", OracleType.VarChar,20),
					new OracleParameter("vPUTINNO", OracleType.NVarChar),
					new OracleParameter("vPUTINDATE", OracleType.DateTime),
					new OracleParameter("vCREATEUSER", OracleType.VarChar,20),
					new OracleParameter("vCREATEDATE", OracleType.DateTime),
					new OracleParameter("vREMARK", OracleType.NVarChar),
                    new OracleParameter("vIssubmit", OracleType.Number),
                    new OracleParameter("varOut", OracleType.VarChar,50),
                    new OracleParameter("vOUTSTORAGEID",OracleType.VarChar,50)};
                    parameters[0].Value = model.ID;
                    parameters[1].Value = model.VERSION;
                    parameters[2].Value = model.CARDKIND;
                    parameters[3].Value = model.BATCHID;
                    parameters[4].Value = model.CARDBEGINSSN;
                    parameters[5].Value = model.CARDENDSSN;
                    parameters[6].Value = model.RECNUM;
                    parameters[7].Value = model.PUTINNO;
                    parameters[8].Value = model.PUTINDATE;
                    parameters[9].Value = model.CREATEUSER;
                    parameters[10].Value = model.CREATEDATE;
                    parameters[11].Value = model.REMARK;
                    parameters[12].Value = model.IsSubmit;
                    parameters[13].Direction = ParameterDirection.Output;
                    parameters[14].Value = model.OUTSTORAGEID;

                    StrOutPar = DbHelperSQL.RunProcedure("P_PutInCard_Update", parameters, parameters[13]).ToString();
                    if (StrOutPar.Split(',')[0] == "ok")
                    {
                        tx.Commit();

                        #region 将制卡坏卡插入缺失卡流水表中
                        StringBuilder sbHK = new StringBuilder();//将制卡坏卡插入缺失卡流水表中
                        sbHK.Append(" begin delete from CARDRECORD_LACK where ZKID='" + model.ID + "'; ");
                        sbHK.Append(" insert into CARDRECORD_LACK(LCARDNO,ZKID) select t.cardno,'" + model.ID + "' HKID from cardrecord t   ");
                        sbHK.Append(" where t.bkchukusj is not null and t.bcprukusj is null and ");
                        sbHK.Append(" BKCHUKUID=(select OUTSTORAGEID from PUTINCARD where ID='" + model.ID + "'); end ;");
                        cmd.CommandText = sbHK.ToString();
                        cmd.ExecuteNonQuery();
                        #endregion
                    }
                    else
                        tx.Rollback();
                }
                catch (System.Data.OracleClient.OracleException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
            }
            return StrOutPar;
        }
        #endregion

        #region 新增
        /// <summary>
        /// 新增信息
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns>返回插入数据的编号</returns>
        public string Add(CardLogistics.Model.PutInCard.PutInCard model)
        {

            try
            {
                OracleParameter[] parameters = {
                    new OracleParameter("varOut", OracleType.VarChar,50),
					new OracleParameter("vVERSION", OracleType.VarChar,50),
					new OracleParameter("vCARDKIND", OracleType.VarChar,10),
					new OracleParameter("vBATCHID", OracleType.VarChar,20),
					new OracleParameter("vCARDBEGINSSN", OracleType.VarChar,20),
					new OracleParameter("vCARDENDSSN", OracleType.VarChar,20),
					new OracleParameter("vRECNUM", OracleType.VarChar,20),
					new OracleParameter("vPUTINNO", OracleType.NVarChar),
					new OracleParameter("vPUTINDATE", OracleType.DateTime),
					new OracleParameter("vCREATEUSER", OracleType.VarChar,20),
					new OracleParameter("vCREATEDATE", OracleType.DateTime),
					new OracleParameter("vREMARK", OracleType.NVarChar),
                    new OracleParameter("vIssubmit", OracleType.Number)};
                parameters[0].Direction = ParameterDirection.Output;
                parameters[1].Value = model.VERSION;
                parameters[2].Value = model.CARDKIND;
                parameters[3].Value = model.BATCHID;
                parameters[4].Value = model.CARDBEGINSSN;
                parameters[5].Value = model.CARDENDSSN;
                parameters[6].Value = model.RECNUM;
                parameters[7].Value = model.PUTINNO;
                parameters[8].Value = model.PUTINDATE;
                parameters[9].Value = model.CREATEUSER;
                parameters[10].Value = model.CREATEDATE;
                parameters[11].Value = model.REMARK;
                parameters[12].Value = model.IsSubmit;

                string StrOutPar = DbHelperSQL.RunProcedure("P_PutInCard_Add", parameters, parameters[0]).ToString();
                return StrOutPar;
            }
            catch (Exception ex)
            {
                return "err," + ex.Message;
            }
        }
        #endregion

        #region 修改
        /// <summary>
        /// 修改信息
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns>返回插入数据的编号</returns>
        public string Update(CardLogistics.Model.PutInCard.PutInCard model)
        {
            try
            {
                OracleParameter[] parameters = {
				    new OracleParameter("vID", OracleType.VarChar,50),
					new OracleParameter("vVERSION", OracleType.VarChar,50),
					new OracleParameter("vCARDKIND", OracleType.VarChar,10),
					new OracleParameter("vBATCHID", OracleType.VarChar,20),
					new OracleParameter("vCARDBEGINSSN", OracleType.VarChar,20),
					new OracleParameter("vCARDENDSSN", OracleType.VarChar,20),
					new OracleParameter("vRECNUM", OracleType.VarChar,20),
					new OracleParameter("vPUTINNO", OracleType.NVarChar),
					new OracleParameter("vPUTINDATE", OracleType.DateTime),
					new OracleParameter("vCREATEUSER", OracleType.VarChar,20),
					new OracleParameter("vCREATEDATE", OracleType.DateTime),
					new OracleParameter("vREMARK", OracleType.NVarChar),
                    new OracleParameter("vIssubmit", OracleType.Number),
                    new OracleParameter("varOut", OracleType.VarChar,50)};
                parameters[0].Value = model.ID;
                parameters[1].Value = model.VERSION;
                parameters[2].Value = model.CARDKIND;
                parameters[3].Value = model.BATCHID;
                parameters[4].Value = model.CARDBEGINSSN;
                parameters[5].Value = model.CARDENDSSN;
                parameters[6].Value = model.RECNUM;
                parameters[7].Value = model.PUTINNO;
                parameters[8].Value = model.PUTINDATE;
                parameters[9].Value = model.CREATEUSER;
                parameters[10].Value = model.CREATEDATE;
                parameters[11].Value = model.REMARK;
                parameters[12].Value = model.IsSubmit;
                parameters[13].Direction = ParameterDirection.Output;
                string StrOutPar = DbHelperSQL.RunProcedure("P_PutInCard_Update", parameters, parameters[13]).ToString();
                return StrOutPar;
            }
            catch (Exception ex)
            {
                return "err," + ex.Message;
            }
        }
        #endregion

        #region 删除
        /// <summary>
        /// 删除信息
        /// </summary>
        /// <param name="id">删除信息的ID</param>
        /// <returns>删除成功返回True。否则返回false</returns>
        public string Delete(string id)
        {
            try
            {
                OracleParameter[] parameters = {
                        new OracleParameter("varOut",  OracleType.VarChar,100),
					    new OracleParameter("vid",  OracleType.VarChar,50)
                };
                parameters[0].Direction = ParameterDirection.Output;
                parameters[1].Value = id;
                string StrOutPar = DbHelperSQL.RunProcedure("P_PutInCard_Delete", parameters, parameters[0]).ToString();
                return StrOutPar;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion
    }
}
